import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

#导入文件，读取数据
store = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/stores.csv')
holidays_events = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/holidays_events.csv')
oil = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/oil.csv')
sample_submission = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/sample_submission.csv')
train = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/train.csv')
test = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/test.csv')
transactions = pd.read_csv('D:/Haozip/store-sales-time-series-forecasting/transactions.csv')

# 查看所有文件详细信息，包括列名、非空值数量和数据类型。
print(test.info())
print(train.info())
print(sample_submission.info())
print(transactions.info())
print(holidays_events.info())
print(oil.info())

# 将date列转换为日期时间格式并按日期排序
oil['date'] = pd.to_datetime(oil['date'])
oil = oil.sort_values('date')
# 创建完整的日期范围
date_range = pd.date_range(start=oil['date'].min(), end=oil['date'].max())
# 重新索引数据框以包含完整的日期范围
oil = oil.set_index('date').reindex(date_range).rename_axis('date').reset_index()
# 用后一天的数据填充缺失值
oil['dcoilwtico'] = oil['dcoilwtico'].bfill()
# 保存处理后的数据回CSV文件
oil.to_csv('oil.csv', index=False)

# 确保箱线图使用的是处理后的数据，而不是原始数据,使用head()函数查看处理后的数据集的开头几行。
print(oil.head())
# 对processed_oil异常值检验,检查dcoilwtico列是否有负值
dcoilwtico_negative = oil[oil['dcoilwtico'] < 0]
# 输出异常值
if not dcoilwtico_negative.empty:
    print("sales列中存在负值，异常值如下：")
    print(sales_negative)
# oil.csv创建箱线图
plt.figure(figsize=(10, 6))
sns.boxplot(x='dcoilwtico', data=oil)
plt.title('Boxplot of dcoilwtico')
plt.show()


# 输出该文件的前五行，查看是否是处理过的数据
print(transactions.head())
# 对transaction.csv异常值检验,检查transaction列是否有负值
transactions_negative = transactions[transactions['transactions'] < 0]
# 输出异常值
if not transactions_negative.empty:
    print("transaction列中存在负值，异常值如下：")
    print(transactions_negative)
# 绘制箱线图来检测交易数量的异常值：
plt.figure(figsize=(8, 6))
sns.boxplot(x='transactions',data= transactions)
plt.title('Boxplot of Transaction')
plt.show()

# 删除值小于10对应的行
transactions = transactions[transactions['transactions'] >= 10]
# 计算交易数量的统计指标，并观察是否存在明显异常值或异常数据分布情况：
print(transactions['transactions'].describe())

# 输出该文件的前五行，查看是否是处理过的数据
print(train.head())
# 对train.csv异常值检验,检查sales列是否有负值
sales_negative = train[train['sales'] < 0]
# 输出异常值
if not sales_negative.empty:
    print("sales列中存在负值，异常值如下：")
    print(sales_negative)
# 绘制箱线图来检测交易数量的异常值：
plt.figure(figsize=(8, 6))
sns.boxplot(x='sales',data= train)
plt.title('Boxplot of Sales')
plt.show()
# 计算交易数量的统计指标，并观察是否存在明显异常值或异常数据分布情况：
print(train['sales'].describe())

# 对transaction数据集提取日期特征
transactions['year'] = pd.to_datetime(transactions['date']).dt.year
transactions['month'] = pd.to_datetime(transactions['date']).dt.month
transactions['day'] = pd.to_datetime(transactions['date']).dt.day

# 对train数据集提取日期特征
train['year'] = pd.to_datetime(train['date']).dt.year
train['month'] = pd.to_datetime(train['date']).dt.month
train['day'] = pd.to_datetime(train['date']).dt.day

# 对oil数据集提取日期特征
oil['year'] = pd.to_datetime(oil['date']).dt.year
oil['month'] = pd.to_datetime(oil['date']).dt.month
oil['day'] = pd.to_datetime(oil['date']).dt.day

# 对于字符串类型的日期列，转换为日期时间格式



# 根据日期特征合并transaction数据集和train数据集
combined_data = pd.merge(transactions, train, on=['date', 'store_nbr'], how='left')
combined_data['date'] = pd.to_datetime(combined_data['date'])
# 根据日期特征合并combined_data和oil数据集
combined_data = pd.merge(combined_data, oil, on='date', how='left')

# 查看合并后的数据集的前几行
print(combined_data.head())

# 对"family"列进行独热编码
combined_data = pd.get_dummies(combined_data, columns=['family'])
# 查看编码后的数据集的前几行
print(combined_data.head())

# 删除不需要的列
combined_data = combined_data.drop(['id', 'date', 'onpromotion'], axis=1)

# 查看更新后的数据集的前几行
print(combined_data.head())

plt.figure(figsize=(8, 6))
sns.boxplot(x='transactions', data=combined_data)
plt.title('Boxplot of combined-data')
plt.show()